package utils;

import com.opencsv.CSVReader;
import com.opencsv.exceptions.CsvValidationException;

import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class SQLTool {
    public SQLTool(){

    }
    public List<String[]> readCSV(String filePath) throws CsvValidationException {
        List<String[]> records = new ArrayList<>();
        try (CSVReader reader = new CSVReader(new FileReader(filePath))) {
            String[] line;
            while ((line = reader.readNext()) != null) {
                records.add(line);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return records;
    }
    public Connection getConnection() {
        String url = "jdbc:mysql://localhost:3306/carinfo";
        String user = "root";
        String password = "LJXAGNB2021";

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            return DriverManager.getConnection(url, user, password);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
    public void batchInsert(List<String[]> records, Connection conn) throws SQLException {
        String sql = "INSERT INTO brand ( date, sale_count,bname) VALUES (?, ?, ?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            for (String[] record : records) {
                pstmt.setString(1, record[0]); // 假设第一列对应column1
                pstmt.setString(2, record[1]); // 第二列对应column2
                pstmt.setString(3, record[2]); // 第二列对应column2
                pstmt.addBatch();
            }
            pstmt.executeBatch();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void main() throws SQLException, CsvValidationException {
        File folder = new File("D:\\ciew\\brand\\");

        List<List<String[]>> allRecords = new ArrayList<>();

        // 遍历文件夹中的所有文件
        for (File file : folder.listFiles()) {
            if (file.isFile() && file.getName().endsWith(".csv")) {
                List<String[]> records = readCSV(file.getAbsolutePath());
                allRecords.add(records);
                Connection conn = getConnection();
                if (conn != null) {
                    batchInsert(records, conn);
                    try {
                        conn.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
    }

}